package com.show.service.unit;

import com.show.dao.unit.CgUnitRepository;
import com.show.dto.sensor.ShaftCoverSensorError;
import com.show.dto.unit.Unit;
import com.show.util.PageUtil;
import com.show.util.QueryUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * 部件service
 *
 * @author liukai
 */
@Service
public class UnitService {

    private QueryUtil queryUtil;

    private CgUnitRepository cgUnitRepository;

    @Autowired
    public UnitService(QueryUtil queryUtil, CgUnitRepository cgUnitRepository) {
        this.queryUtil = queryUtil;
        this.cgUnitRepository = cgUnitRepository;
    }


    /**
     * 查询全部井盖数量
     *
     * @return long 数量
     */
    public long shaftCount() {
        return cgUnitRepository.count();
    }

    /**
     * 根据部件id获取部件信息
     *
     * @param unitId 部件id
     * @author liukai
     */
    public Unit findOne(String unitId) {
        String sql = "SELECT\n" +
                "  (CASE\n" +
                "   WHEN DATE_FORMAT(CURDATE(), '%Y%m%d') > DATE_FORMAT(unit.finish_date, '%Y%m%d')\n" +
                "     THEN '已过期'\n" +
                "   WHEN DATE_FORMAT(CURDATE(), '%Y%m%d') >= date_sub(DATE_FORMAT(unit.finish_date, '%Y%m%d'), INTERVAL 1 MONTH)\n" +
                "        AND DATE_FORMAT(CURDATE(), '%Y%m%d') <= DATE_FORMAT(unit.finish_date, '%Y%m%d')\n" +
                "     THEN '即将过期'\n" +
                "   ELSE\n" +
                "     '未过期'\n" +
                "   END)                   AS exceedTimeFlag,\n" +
                "  unit.*,\n" +
                "  unit.unirestst_id       AS rests," +
                "  unitType.unit_type_name AS unitTypeName,\n" +
                "  unitType.unit_type_id   AS unitTypeId,\n" +
                "  grid.grid_id            AS gridId,\n" +
                "  grid.grid_name          AS gridName,\n" +
                "  org1.org_name           AS deptMainName,\n" +
                "\n" +
                "  org2.org_name           AS deptOwnerName,\n" +
                "\n" +
                "  org3.org_name           AS deptKeepName\n" +
                "\n" +
                "FROM cg_unit unit\n" +
                "  LEFT JOIN unit_type unitType ON unitType.unit_type_id = unit.unit_type_id\n" +
                "  LEFT JOIN cg_grid grid ON grid.grid_id = unit.grid_id\n" +
                "  LEFT JOIN cg_org org1 ON org1.org_id = unit.deptmain_id\n" +
                "  LEFT JOIN cg_org org2 ON org2.org_id = unit.deptowner_id\n" +
                "  LEFT JOIN cg_org org3 ON org3.org_id = unit.deptkeep_id\n" +
                "WHERE 1 = 1 AND unit.sts = '0' AND org1.sts = '0' AND org2.sts = '0' AND org3.sts = '0'\n" +
                "      AND unit.unit_id = '" + unitId + "'";
        List<Unit> unitList = queryUtil.query(sql, Unit.class);
        System.out.println(sql);
        if (unitList.size() > 0) {
            return unitList.get(0);
        } else {
            return null;
        }
    }


    public Unit getUnitBySmId(String smId) {
        String sql = "SELECT\n" +
                "  unit.*,\n" +
                "  org1.org_name                        AS deptMainName,\n" +
                "  org2.org_name                        AS deptOwnerName,\n" +
                "  org3.org_name                        AS deptKeepName,\n" +
                "  shaftCoverType.shaft_cover_type_name as shaftCoverTypeName,\n" +
                "  emp.emp_name                         as empName\n" +
                "FROM cg_unit unit\n" +
                "  left join shaft_cover_type shaftCoverType on shaftCoverType.shaft_cover_type_id = unit.shaft_cover_type_id\n" +
                "  LEFT JOIN cg_org org1 ON org1.org_id = unit.deptmain_id\n" +
                "  LEFT JOIN cg_org org2 ON org2.org_id = unit.deptowner_id\n" +
                "  LEFT JOIN cg_org org3 ON org3.org_id = unit.deptkeep_id\n" +
                "  left join cg_emp emp on emp.emp_id = unit.emp_id\n" +
                "WHERE unit.sts = '0'\n" +
                "      AND org1.sts = '0'\n" +
                "      AND org2.sts = '0'\n" +
                "      AND org3.sts = '0'\n" +
                "      AND unit.smid = '" + smId + "'";
        System.out.println(sql);
        List<Unit> unitList = queryUtil.query(sql, Unit.class);
        if (unitList.size() > 0) {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyy年MM月dd日 HH时mm分ss秒");
            String date = sdf.format(new Date());
            unitList.get(0).setCdate(date);


            String sql1 = "select count(*)\n" +
                    "from shaft_cover_sensor_error error\n" +
                    "  left join shaft_cover_sensor shaft on shaft.shaft_cover_sensor_id = error.shaft_cover_sensor_id\n" +
                    "  left join cg_unit unit on unit.shaft_cover_sensor_id = shaft.shaft_cover_sensor_id\n" +
                    "where unit.unit_id = '" + unitList.get(0).getUnitId() + "'";
            int size = queryUtil.query(sql1, ShaftCoverSensorError.class).size();
            if (size > 0) {
                unitList.get(0).setStatusName("报警");
            } else {
                unitList.get(0).setStatusName("正常");
            }


            return unitList.get(0);
        } else {
            return null;
        }

    }


    /**
     * 查询所有报警的井盖
     *
     * @return List
     */
    public List<Unit> queryErrorUnitList() {
        String sql = "select\n" +
                "  unit.*,\n" +
                "  org1.org_name           AS deptMainName,\n" +
                "  org2.org_name           AS deptOwnerName,\n" +
                "  org3.org_name           AS deptKeepName,\n" +
                "  error.status            as status,\n" +
                "  error.status_name       as statusName,\n" +
                "  unitType.unit_type_name as unitTypeName\n" +
                "from shaft_cover_sensor_error error\n" +
                "  left join shaft_cover_sensor shaft on shaft.shaft_cover_sensor_id = error.shaft_cover_sensor_id\n" +
                "  left join cg_unit unit on unit.shaft_cover_sensor_id = shaft.shaft_cover_sensor_id\n" +
                "  LEFT JOIN unit_type unitType ON unitType.unit_type_id = unit.unit_type_id\n" +
                "  LEFT JOIN cg_org org1 ON org1.org_id = unit.deptmain_id\n" +
                "  LEFT JOIN cg_org org2 ON org2.org_id = unit.deptowner_id\n" +
                "  LEFT JOIN cg_org org3 ON org3.org_id = unit.deptkeep_id\n" +
                "WHERE unit.sts = '0'\n" +
                "      AND org1.sts = '0'\n" +
                "      AND org2.sts = '0'\n" +
                "      AND org3.sts = '0'\n" +
                "group by error.shaft_cover_sensor_id";
        System.out.println(sql);
        return queryUtil.query(sql, Unit.class);
    }

    /**
     * 获得部件类型
     *
     * @param ids 部件类型id数组
     * @author liukai
     */
    public PageUtil<Unit> getUnitByTypeId(String[] ids, Integer page, Integer pageSize) {
        if (ids.length > 0) {
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT\n" +
                    "  unit.unit_id        AS unitId,\n" +
                    "  unit.unit_code      AS unitCode,\n" +
                    "  type.unit_type_name AS unitTypeName,\n" +
                    "  grid.grid_name      AS gridName\n" +
                    "FROM\n" +
                    "  cg_unit unit\n" +
                    "  LEFT JOIN unit_type AS type ON unit.unit_type_id = type.unit_type_id\n" +
                    "  LEFT JOIN cg_grid grid ON grid.grid_id = unit.grid_id\n" +
                    "WHERE\n" +
                    "  1 = 1\n" +
                    "  AND unit.sts = '0'\n" +
                    "  AND type.unit_type_id IN (");
            for (int i = 0; i < ids.length; i++) {
                sql.append(ids[i]);
                if (i < ids.length - 1) {
                    sql.append(",");
                }
            }
            sql.append(")");
            return queryUtil.queryByPage(sql.toString(), Unit.class, page, pageSize);
        } else {
            return null;
        }
    }

}
